/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package company.allocation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.naming.NamingException;
import system.general.Exc;
import system.general.Label;
import system.general.Logged;
import system.general.Message;
import system.general.Persistencia;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "company_allocation")
@RequestScoped
public class Main extends Beans {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Message msg = new Message();
    private Find allocation_companyFind = new Find();
    private List<Beans> allocation_companyList = new ArrayList<>();
    private String sql;
    private Map<String, String> map = new LinkedHashMap<>();
    private gps.general.Find gps_generalFind = new gps.general.Find();
    private company.general.Find company_generalFind = new company.general.Find();
    //Mapa encargado de contener los valores booleanos para la lista 
    private Map<Integer, Boolean> checked = new LinkedHashMap<>();
    //Mapa encargado de contener los valores booleanos para la de la caducidad de los gps
    private Map<Integer, Boolean> caduca = new LinkedHashMap<>();
    //Lista con todos los valores corresponedientes a la lista desplegada en DataList
    //alamacena el estado y los dias de acuerdo a el estado estado 1 = caduca , 2= dias restantes , 3= activo 
    private Map<Integer, String> state = new LinkedHashMap<>();
    private List<Beans> tableList = new ArrayList<>();
    private Label label = new Label();
    private int dias = 0;

    public Main() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
    }

    private void load() {
        try {
            map.clear();
            map.put("al.id = " + logged.getDetailId(), " ");

            allocation_companyList.clear();
            allocation_companyList = allocation_companyFind.byMap(map);

            setId(allocation_companyList.get(0).getId());
            getGpsBeans().setId(allocation_companyList.get(0).getGpsBeans().getId());
            getGpsBeans().setImei(allocation_companyList.get(0).getGpsBeans().getImei());
            getCompanyBeans().setId(allocation_companyList.get(0).getCompanyBeans().getId());
            getCompanyBeans().setNombre(allocation_companyList.get(0).getCompanyBeans().getNombre());

        } catch (Exc ex) {
            msg.showMsg(namePackage(), "system", ex.getIdError());
        } catch (SQLException ex) {
            msg.showErr(namePackage(), "sql", ex);
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
        }
    }

    public void insert() {
        try {

            map.clear();
            map.put("id_compania = " + getCompanyBeans().getId(), " AND ");
            map.put("id_gps = " + getGpsBeans().getId(), "  ");
            if (allocation_companyFind.byExist(map)) {
                throw new Exc("m5");
            }

            sql = "INSERT INTO public.compania_gps (id_compania, id_gps) "
                    + "VALUES (" + getCompanyBeans().getId() + ","
                    + getGpsBeans().getId() + ")";


            //Inserta la información del registro
            persistencia.executeUpdate(sql);

            //Localiza el id del registro recien insertado, con la llave única
            allocation_companyList = allocation_companyFind.byMap(map);

            //Asigna el id del registro insertado a la variable global
            logged.setDetailId(allocation_companyList.get(0).getId());

            throw new Exc("m1");

        } catch (Exc ex) {
            msg.showMsg(namePackage(), "system", ex.getIdError());
        } catch (SQLException ex) {
            msg.showErr(namePackage(), "sql", ex);
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
        }
    }

    private void deleteById(int id) throws Exc, SQLException, Exception {
        //Verifica si el usuario ya existe para otra persona
        sql = "DELETE FROM public.compania_gps "
                + "WHERE id = " + id;
        persistencia.executeUpdate(sql);
    }

    public List<Beans> listTable() {

        try {
            tableList.clear();
            getChecked().clear();
            int listIndex = 0;

            /*Selecciona todos los gps que tiene la compañia*/
            sql = "SELECT cgps.id as id_detail, gps.id as id_gps, gps.imei, gps.marca, gps.modelo,"
                    + "gps.caducidad ,CURRENT_DATE as actual,"
                    + " cp.nombre as nombre_compania FROM public.v_gps gps "
                    + "LEFT JOIN public.v_compania_gps cgps ON (gps.id = cgps.id_gps) "
                    + "LEFT JOIN public.v_compania cp ON (cp.id = cgps.id_compania) WHERE "
                    + "cgps.id_compania=" + logged.getMasterId() + " ORDER BY gps.marca ASC";

            listIndex = fillTableList(sql, listIndex);

            /*Selecciona todos los gps que aun no han sido asignados a alguna compañia*/
            sql = "SELECT cgps.id as id_detail, gps.id as id_gps, gps.imei, gps.marca, gps.modelo,"
                    + " gps.caducidad ,CURRENT_DATE as actual , "
                    + "cp.nombre as nombre_compania FROM public.v_gps gps "
                    + "LEFT JOIN public.v_compania_gps cgps ON (gps.id = cgps.id_gps) "
                    + "LEFT JOIN public.v_compania cp ON (cp.id = cgps.id_compania) WHERE "
                    + "gps.id NOT IN (SELECT id_gps FROM public.v_compania_gps) ORDER BY gps.marca ASC";

            fillTableList(sql, listIndex);

            if (tableList.isEmpty()) {
                return null;
            }

            return tableList;
        } catch (SQLException ex) {
            msg.showErr(namePackage(), "sql", ex);
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
        }

        return null;
    }

    private int fillTableList(String sql, int index) throws Exc, SQLException, SQLException, NamingException, Exception {
        ResultSet rsts = persistencia.executeRsts(sql);
        int listIndex = index;
        boolean check, val;
        Date caducidad = new java.util.Date();
        Date actual = new java.util.Date();

        while (rsts.next()) {

            caducidad = rsts.getDate("caducidad");
            Beans bean = new Beans(
                    rsts.getInt("id_detail"),
                    logged.getMasterId(),
                    rsts.getInt("id_gps"),
                    rsts.getString("nombre_compania"),
                    rsts.getString("imei"),
                    caducidad);

            bean.getGpsBeans().setMarca(rsts.getString("marca"));
            bean.getGpsBeans().setModelo(rsts.getString("modelo"));
            actual = rsts.getDate("actual");
            tableList.add(bean);

            //verifica si esta asignado a una compañia o no
            if ((bean.getCompanyBeans().getNombre() != null) && !bean.getCompanyBeans().getNombre().isEmpty()) {
                check = true;
            } else {
                check = false;
            }
            getChecked().put(tableList.get(listIndex).getGpsBeans().getId(), check);
            dias = ((int) (Math.floor((caducidad.getTime() - actual.getTime()) / (1000 * 60 * 60 * 24))));
            if (caducidad.compareTo(actual) <= 0) {
                // aun se puede asignar 
                val = true;
                getState().put(tableList.get(listIndex).getId(), label.name(namePackage(), "estado1") + " ");
            } else {
                if (dias >= 0 & dias <= 30) {
                    getState().put(tableList.get(listIndex).getId(), label.name(namePackage(), "estado2") + " " + dias);
                } else {
                    getState().put(tableList.get(listIndex).getId(), label.name(namePackage(), "estado3"));
                }
                val = false;
            }
            getCaduca().put(tableList.get(listIndex).getId(), val);
            listIndex++;
        }
        persistencia.dbDisconnect();
        return listIndex;
    }

    public void clear() {
        logged.setDetailId(0);
        clearBeans();
    }

    public void selectedTempId(int tempId) {
        logged.setDetailId(tempId);
        load();
    }

    private String namePackage() {
        Package pack = this.getClass().getPackage();
        return pack.getName();
    }

    public List<SelectItem> getGPSItemsList() {
        map.clear();
        map.put("g.caducidad ISNULL ", " ");
        try {
            List<gps.general.Beans> beansList = new ArrayList<>();

            sql = "SELECT * FROM public.v_gps gps WHERE gps.id NOT IN (SELECT id_gps FROM public.v_compania_gps)";
            ResultSet rsts = persistencia.executeRsts(sql);
            while (rsts.next()) {
                gps.general.Beans bean = new gps.general.Beans(
                        rsts.getInt("id"),
                        rsts.getString("imei"),
                        rsts.getString("numero_serie"),
                        rsts.getString("marca"),
                        rsts.getString("modelo"),
                        rsts.getString("nombre"),
                        rsts.getInt("tipo"),
                        rsts.getString("detalle"),
                        rsts.getTimestamp("caducidad"),
                        rsts.getString("telefono"));
                beansList.add(bean);
            }

            persistencia.dbDisconnect();

            if (beansList.isEmpty()) {
                return null;
            }
            ArrayList<SelectItem> itemList = new ArrayList<>();
            for (int i = 0; i < beansList.size(); i++) {
                itemList.add(new SelectItem(beansList.get(i).getId().toString(), beansList.get(i).getImei()));
            }
            return itemList;
        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<SelectItem> getCompanyItemsList() {
        map.clear();
        map.put("lower(nombre) SIMILAR TO '%()%' ", " ");
        try {
            return company_generalFind.listItem(map);
        } catch (Exc ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void updateTable() {

        Iterator iterator = getChecked().entrySet().iterator();
        String company;
        int row = 0;

        try {
            while (iterator.hasNext()) {
                Map.Entry<Integer, Boolean> mapEntry = (Map.Entry) iterator.next();
                company = tableList.get(row).getCompanyBeans().getNombre();


                /*si esta desseleccionado y tiene nombre de compañia borralo*/
                if (!mapEntry.getValue() && (company != null && !company.isEmpty())) {
                    deleteById(tableList.get(row).getId());
                } /*de otro modo si esta seleccionado insertalo*/ else if (mapEntry.getValue() && (company == null || company.isEmpty())) {
                    sql = "INSERT INTO public.compania_gps (id_compania, id_gps) "
                            + "VALUES (" + logged.getMasterId() + ","
                            + mapEntry.getKey() + ")";

                    //Inserta la información del registro
                    persistencia.executeUpdate(sql);
                }

                row++;
            }
        } catch (Exc ex) {
            msg.showMsg(namePackage(), "system", ex.getIdError());
        } catch (SQLException ex) {
            msg.showErr(namePackage(), "sql", ex);
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
        }
    }

    public Map<Integer, Boolean> getChecked() {
        return checked;
    }

    public void setChecked(Map<Integer, Boolean> checked) {
        this.checked = checked;
    }

    public Map<Integer, Boolean> getCaduca() {
        return caduca;
    }

    public void setCaduca(Map<Integer, Boolean> caduca) {
        this.caduca = caduca;
    }

    public Map<Integer, String> getState() {
        return state;
    }

    public void setState(Map<Integer, String> state) {
        this.state = state;
    }
}
